﻿using System;
using System.Data.SqlClient;
using System.Data;
using System.Collections.Generic;
using System.Text;

using Model;
namespace DAL
{
    #region class SQLDALZKZ
    /// <summary>
    /// it is suitable to the table with only one primary key,and can not deal with the aotu-increment key.
    /// Note:the first field in the table must be primary and autoincrement and int ,if exists 
    /// class:DAL ZKZ 
    /// Author:wxc
    /// DateTime:2021/5/27 11:15:43
    /// </summary>	
    public class DALZKZ
    {
        #region SQL CONST		
        private const string INSERT_SQL = "INSERT INTO [ZKZ] ([ID],[Name],[Sex],[Number],[KCnum],[Seat],[DCollage],[RCollage],[Subject],[KSTime],[KCaddress]) Values (@ID,@Name,@Sex,@Number,@KCnum,@Seat,@DCollage,@RCollage,@Subject,@KSTime,@KCaddress)";
        private const string UPDATE_SQL = "UPDATE [ZKZ] SET Name=@Name,Sex=@Sex,Number=@Number,KCnum=@KCnum,Seat=@Seat,DCollage=@DCollage,RCollage=@RCollage,Subject=@Subject,KSTime=@KSTime,KCaddress=@KCaddress WHERE [ID]=@ID";
        private const string DEL_SQL = "DELETE FROM [ZKZ] WHERE [ID]=@ID";
        private const string SELECT_ALL_SQL = "SELECT [ID],[Name],[Sex],[Number],[KCnum],[Seat],[DCollage],[RCollage],[Subject],[KSTime],[KCaddress] FROM [ZKZ] order by iD desc";
        private const string SELECT_ONE_SQL = "SELECT [ID],[Name],[Sex],[Number],[KCnum],[Seat],[DCollage],[RCollage],[Subject],[KSTime],[KCaddress] FROM [ZKZ] WHERE [ID]=@ID";
        private const string SELECT_WHERE_SQL = "SELECT [ID],[Name],[Sex],[Number],[KCnum],[Seat],[DCollage],[RCollage],[Subject],[KSTime],[KCaddress] FROM [ZKZ] {$where} order by  iD desc";
        #endregion
        #region Paras const
        private const string PARA_ID = "@ID";
        private const string PARA_Name = "@Name";
        private const string PARA_Sex = "@Sex";
        private const string PARA_Number = "@Number";
        private const string PARA_KCnum = "@KCnum";
        private const string PARA_Seat = "@Seat";
        private const string PARA_DCollage = "@DCollage";
        private const string PARA_RCollage = "@RCollage";
        private const string PARA_Subject = "@Subject";
        private const string PARA_KSTime = "@KSTime";
        private const string PARA_KCaddress = "@KCaddress";
        #endregion
        #region Add Method
        /// <summary>
        /// Insert an item
        /// </summary>
        /// <param name="ZKZ"></param>
        /// <returns>count of rows affected</returns>
        public int AddZKZ(ZKZEntity ZKZ)
        {
            int backvalue = 0;
            //get paras
            SqlParameter[] ZKZParms = GetZKZParametersInsert();
            //set values to paras
            ZKZParms[0].Value = ZKZ.ID;
            ZKZParms[1].Value = ZKZ.Name;
            ZKZParms[2].Value = ZKZ.Sex;
            ZKZParms[3].Value = ZKZ.Number;
            ZKZParms[4].Value = ZKZ.KCnum;
            ZKZParms[5].Value = ZKZ.Seat;
            ZKZParms[6].Value = ZKZ.DCollage;
            ZKZParms[7].Value = ZKZ.RCollage;
            ZKZParms[8].Value = ZKZ.Subject;
            ZKZParms[9].Value = ZKZ.KSTime;
            ZKZParms[10].Value = ZKZ.KCaddress;
            //insert			
            using (SqlConnection conn = new SqlConnection(SQLHelper.ConnectionString))
            {
                backvalue = SQLHelper.ExecuteNonQuery(SQLHelper.ConnectionString, CommandType.Text, INSERT_SQL, ZKZParms);
            }
            return backvalue;
        }
        #endregion
        #region Mod Method
        /// <summary>
        /// Update an item
        /// </summary>
        /// <param name="ZKZ"></param>
        /// <returns>count of rows affected</returns>
        public int ModZKZ(ZKZEntity ZKZ)
        {
            int backvalue = 0;
            //get paras
            SqlParameter[] ZKZParms = GetZKZParameters();
            //set values to paras
            ZKZParms[0].Value = ZKZ.ID;
            ZKZParms[1].Value = ZKZ.Name;
            ZKZParms[2].Value = ZKZ.Sex;
            ZKZParms[3].Value = ZKZ.Number;
            ZKZParms[4].Value = ZKZ.KCnum;
            ZKZParms[5].Value = ZKZ.Seat;
            ZKZParms[6].Value = ZKZ.DCollage;
            ZKZParms[7].Value = ZKZ.RCollage;
            ZKZParms[8].Value = ZKZ.Subject;
            ZKZParms[9].Value = ZKZ.KSTime;
            ZKZParms[10].Value = ZKZ.KCaddress;
            //update			
            using (SqlConnection conn = new SqlConnection(SQLHelper.ConnectionString))
            {
                backvalue = SQLHelper.ExecuteNonQuery(SQLHelper.ConnectionString, CommandType.Text, UPDATE_SQL, ZKZParms);
            }
            return backvalue;
        }
        #endregion
        #region Del Method
        /// <summary>
        /// Del an item by primary key
        /// </summary>
        /// <param name="iD"></param> 
        /// <returns>count of rows affected</returns>
        public int DelZKZ(string iD)
        {
            int backvalue = 0;
            //primarykeys
            //get paras
            SqlParameter[] ZKZParms = GetZKZPrimaryKeyParameters();
            //set values to paras
            ZKZParms[0].Value = iD;
            //delete
            using (SqlConnection conn = new SqlConnection(SQLHelper.ConnectionString))
            {
                backvalue = SQLHelper.ExecuteNonQuery(SQLHelper.ConnectionString, CommandType.Text, DEL_SQL, ZKZParms);
            }
            return backvalue;
        }
        #endregion
        #region Select Method
        /// <summary>
        /// Select an item by Primarykey
        /// </summary>
        /// <param name="iD"></param>
        /// <returns>an item</returns>
        public ZKZEntity GetZKZ(string iD)
        {
            ZKZEntity ZKZ = new ZKZEntity();
            //primarykeys
            //get paras
            SqlParameter[] ZKZParms = GetZKZPrimaryKeyParameters();
            //set values to paras
            ZKZParms[0].Value = iD;
            //select
            using (SqlDataReader reader = SQLHelper.ExecuteReader(SQLHelper.ConnectionString, CommandType.Text, SELECT_ONE_SQL, ZKZParms))
            {
                if (reader.Read())
                {
                    if (!reader.IsDBNull(0)) ZKZ.ID = reader.GetString(0);
                    if (!reader.IsDBNull(1)) ZKZ.Name = reader.GetString(1);
                    if (!reader.IsDBNull(2)) ZKZ.Sex = reader.GetString(2);
                    if (!reader.IsDBNull(3)) ZKZ.Number = reader.GetString(3);
                    if (!reader.IsDBNull(4)) ZKZ.KCnum = reader.GetString(4);
                    if (!reader.IsDBNull(5)) ZKZ.Seat = reader.GetString(5);
                    if (!reader.IsDBNull(6)) ZKZ.DCollage = reader.GetString(6);
                    if (!reader.IsDBNull(7)) ZKZ.RCollage = reader.GetString(7);
                    if (!reader.IsDBNull(8)) ZKZ.Subject = reader.GetString(8);
                    if (!reader.IsDBNull(9)) ZKZ.KSTime = reader.GetDateTime(9);
                    if (!reader.IsDBNull(10)) ZKZ.KCaddress = reader.GetString(10);
                }
                else
                {
                    ZKZ = null;
                }
            }
            return ZKZ;

        }
        #endregion
        #region Select_All Method
        /// <summary>
        /// Select all items
        /// </summary>
        /// <returns>items</returns>
        public IList<ZKZEntity> GetZKZs()
        {
            IList<ZKZEntity> ZKZs = new List<ZKZEntity>();
            using (SqlDataReader reader = SQLHelper.ExecuteReader(SQLHelper.ConnectionString, CommandType.Text, SELECT_ALL_SQL, null))
            {
                while (reader.Read())
                {
                    ZKZEntity ZKZ = new ZKZEntity();
                    if (!reader.IsDBNull(0)) ZKZ.ID = reader.GetString(0);
                    if (!reader.IsDBNull(1)) ZKZ.Name = reader.GetString(1);
                    if (!reader.IsDBNull(2)) ZKZ.Sex = reader.GetString(2);
                    if (!reader.IsDBNull(3)) ZKZ.Number = reader.GetString(3);
                    if (!reader.IsDBNull(4)) ZKZ.KCnum = reader.GetString(4);
                    if (!reader.IsDBNull(5)) ZKZ.Seat = reader.GetString(5);
                    if (!reader.IsDBNull(6)) ZKZ.DCollage = reader.GetString(6);
                    if (!reader.IsDBNull(7)) ZKZ.RCollage = reader.GetString(7);
                    if (!reader.IsDBNull(8)) ZKZ.Subject = reader.GetString(8);
                    if (!reader.IsDBNull(9)) ZKZ.KSTime = reader.GetDateTime(9);
                    if (!reader.IsDBNull(10)) ZKZ.KCaddress = reader.GetString(10);
                    ZKZs.Add(ZKZ);
                }
            }
            return ZKZs;
        }
        #endregion
        #region private common select by conditions
        /// <summary>
        /// private common select by conditions
        /// </summary>
        /// <returns>IList<ZKZEntity></returns>
        private IList<ZKZEntity> GetZKZsbyQueryString(string SQLstr)
        {
            IList<ZKZEntity> ZKZs = new List<ZKZEntity>();
            using (SqlDataReader reader = SQLHelper.ExecuteReader(SQLHelper.ConnectionString, CommandType.Text, SQLstr))
            {
                while (reader.Read())
                {
                    ZKZEntity ZKZ = new ZKZEntity();
                    if (!reader.IsDBNull(0)) ZKZ.ID = reader.GetString(0);
                    if (!reader.IsDBNull(1)) ZKZ.Name = reader.GetString(1);
                    if (!reader.IsDBNull(2)) ZKZ.Sex = reader.GetString(2);
                    if (!reader.IsDBNull(3)) ZKZ.Number = reader.GetString(3);
                    if (!reader.IsDBNull(4)) ZKZ.KCnum = reader.GetString(4);
                    if (!reader.IsDBNull(5)) ZKZ.Seat = reader.GetString(5);
                    if (!reader.IsDBNull(6)) ZKZ.DCollage = reader.GetString(6);
                    if (!reader.IsDBNull(7)) ZKZ.RCollage = reader.GetString(7);
                    if (!reader.IsDBNull(8)) ZKZ.Subject = reader.GetString(8);
                    if (!reader.IsDBNull(9)) ZKZ.KSTime = reader.GetDateTime(9);
                    if (!reader.IsDBNull(10)) ZKZ.KCaddress = reader.GetString(10);
                    ZKZs.Add(ZKZ);
                }
            }
            return ZKZs;
        }
        #endregion
        #region public common select by conditions ,we can use it by changing the GetZKZsbyCondition(datatype condition) and sql
        /// <summary>
        /// Select items by Columns
        /// </summary>
        /// <returns>IList<ZKZEntity></returns>
        public IList<ZKZEntity> GetZKZsbyCondition(string sql)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(SELECT_WHERE_SQL);
            sb.Replace("{$where}", "where " + sql);
            return GetZKZsbyQueryString(sb.ToString());
        }
        #endregion
        #region Select Method Top n
        /// <summary>
        /// Select items by Columns
        /// </summary>
        /// <returns>IList<ZKZEntity></returns>
        public IList<ZKZEntity> GetZKZsTopN(int n)
        {
            IList<ZKZEntity> ZKZs = new List<ZKZEntity>();
            using (SqlDataReader reader = SQLHelper.ExecuteReader(SQLHelper.ConnectionString, CommandType.Text, SELECT_ALL_SQL, null))
            {
                int i = 0;
                while (reader.Read() && i < n)
                {
                    ZKZEntity ZKZ = new ZKZEntity();
                    if (!reader.IsDBNull(0)) ZKZ.ID = reader.GetString(0);
                    if (!reader.IsDBNull(1)) ZKZ.Name = reader.GetString(1);
                    if (!reader.IsDBNull(2)) ZKZ.Sex = reader.GetString(2);
                    if (!reader.IsDBNull(3)) ZKZ.Number = reader.GetString(3);
                    if (!reader.IsDBNull(4)) ZKZ.KCnum = reader.GetString(4);
                    if (!reader.IsDBNull(5)) ZKZ.Seat = reader.GetString(5);
                    if (!reader.IsDBNull(6)) ZKZ.DCollage = reader.GetString(6);
                    if (!reader.IsDBNull(7)) ZKZ.RCollage = reader.GetString(7);
                    if (!reader.IsDBNull(8)) ZKZ.Subject = reader.GetString(8);
                    if (!reader.IsDBNull(9)) ZKZ.KSTime = reader.GetDateTime(9);
                    if (!reader.IsDBNull(10)) ZKZ.KCaddress = reader.GetString(10);
                    ZKZs.Add(ZKZ);
                    i++;
                }
            }
            return ZKZs;
        }
        #endregion
        #region paras configuration
        /// <summary>
        /// Internal function to get cached parameters:all paras --- suitable to insert ,especially autoincrement
        /// </summary>
        /// <returns></returns>
        private static SqlParameter[] GetZKZParametersInsert()
        {
            SqlParameter[] parms = SQLHelper.GetCachedParameters("SQL_ALL_ZKZ_Insert");

            if (parms == null)
            {
                parms = new SqlParameter[] {
            new SqlParameter(PARA_ID,SqlDbType.NVarChar) ,
            new SqlParameter(PARA_Name,SqlDbType.NVarChar) ,
            new SqlParameter(PARA_Sex,SqlDbType.VarChar) ,
            new SqlParameter(PARA_Number,SqlDbType.NVarChar) ,
            new SqlParameter(PARA_KCnum,SqlDbType.NVarChar) ,
            new SqlParameter(PARA_Seat,SqlDbType.NVarChar) ,
            new SqlParameter(PARA_DCollage,SqlDbType.NVarChar) ,
            new SqlParameter(PARA_RCollage,SqlDbType.NVarChar) ,
            new SqlParameter(PARA_Subject,SqlDbType.NVarChar) ,
            new SqlParameter(PARA_KSTime,SqlDbType.DateTime2) ,
            new SqlParameter(PARA_KCaddress,SqlDbType.NVarChar)         };
                SQLHelper.CacheParameters("SQL_ALL_ZKZ_Insert", parms);
            }
            return parms;
        }
        /// <summary>
        /// Internal function to get cached parameters:all paras --- suitable to update
        /// </summary>
        /// <returns></returns>
        private static SqlParameter[] GetZKZParameters()
        {
            SqlParameter[] parms = SQLHelper.GetCachedParameters("SQL_ALL_ZKZ_Update");

            if (parms == null)
            {
                parms = new SqlParameter[] {
                    new SqlParameter(PARA_ID,SqlDbType.NVarChar) ,
                    new SqlParameter(PARA_Name,SqlDbType.NVarChar) ,
                    new SqlParameter(PARA_Sex,SqlDbType.VarChar) ,
                    new SqlParameter(PARA_Number,SqlDbType.NVarChar) ,
                    new SqlParameter(PARA_KCnum,SqlDbType.NVarChar) ,
                    new SqlParameter(PARA_Seat,SqlDbType.NVarChar) ,
                    new SqlParameter(PARA_DCollage,SqlDbType.NVarChar) ,
                    new SqlParameter(PARA_RCollage,SqlDbType.NVarChar) ,
                    new SqlParameter(PARA_Subject,SqlDbType.NVarChar) ,
                    new SqlParameter(PARA_KSTime,SqlDbType.DateTime2) ,
                    new SqlParameter(PARA_KCaddress,SqlDbType.NVarChar)                 };
                SQLHelper.CacheParameters("SQL_ALL_ZKZ_Update", parms);
            }
            return parms;
        }
        /// <summary>
        /// Internal function to get cached parameters:primary key paras
        /// </summary>
        /// <returns></returns>
        private static SqlParameter[] GetZKZPrimaryKeyParameters()
        {
            SqlParameter[] parms = SQLHelper.GetCachedParameters("SQL_PrimaryKey_ZKZ");

            if (parms == null)
            {
                parms = new SqlParameter[] {
                    new SqlParameter(PARA_ID,SqlDbType.NVarChar) ,
                    };
                SQLHelper.CacheParameters("SQL_PrimaryKey_ZKZ", parms);
            }
            return parms;
        }
        #endregion
    }
    #endregion
}
